| |
197 Search Results Found |
| 1 |
Forum: Data Guard «»
Posted on: Sat, 11 February 2023 11:31 «»
By: Andrey_R
Re: RMAN-06820 on rman backup from standby with OS authentication
…you actually do the test, rather than merely speculating, it may become clear that the datafile backup is 100% useless without the redo required to make it consistent.
That's a fair point. So I did:
- Install Oracle 19c EE, no PDB, on Windows Server…
|
| 2 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 13:32 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…is another possible version with tests of various number values for n.
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID…
|
| 3 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 13:30 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…see some problems with that. Please see my tests below using various number values for n, including 1, which returns no rows.
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, …
|
| 4 |
Forum: Text & interMedia «»
Posted on: Thu, 10 August 2023 19:05 «»
By: Barbara Boehmer
Re: How to determine last tiime Content was indexed
…see the following demonstration and answers below.
-- table, initial data, index, additional data for testing:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_tab
2 (id NUMBER,
3 document VARCHAR2(…
|
| 5 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 10 March 2023 13:25 «»
By: Barbara Boehmer
Re: Suggestion on count via sql statement
…method:
SCOTT@orcl_12.1.0.2.0> SELECT Name, TRUNC(TEST_Date) AS Test_date, Instrument, COUNT(*) AS count
2 FROM test
3 WHERE Instrument IN ('Down', 'Not available')
4 GROUP BY Name, TRUNC(TEST_Date), Instrument
5 UNION
6 …
|
| 6 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 05 April 2023 12:25 «»
By: Barbara Boehmer
Re: difficult task
…tables and such look good, although you may want to make changes later.
For your procedure, this is what you have so far.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE calculate_positions(
2 vInDateTime IN DATE;
3 )
4 AS
5 …
|
| 7 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 09 February 2024 13:17 «»
By: Barbara Boehmer
Re: Xmlattribute not returning tag when the value is null
…am assuming that this is a simplification, and that your data comes from a table with multiple rows and that any value in any column in any row may be null. If this is the case, then after reviewing some of the links from the search link that Michel…
|
| 8 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 08 February 2023 20:27 «»
By: Barbara Boehmer
Re: Convert Date formats
…should be comparing dates as dates, not converting them to character strings and comparing those.
Assuming that you have the following table and data
SCOTT@orcl_12.1.0.2.0> create table mytable (date2 date)
2 /
Table created.
SCOTT@…
|
| 9 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 10:35 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID
--------------- ----------
1 165949
1 165950…
|
| 10 |
Forum: Server Administration «»
Posted on: Tue, 24 May 2022 04:08 «»
By: shamsad.khan@gmail.com
Unable to connect to pluggable database - Oracle Datase 12.2.0.1.0
…
I have installed oracle database 12.2.0.1.0. as container database with orclpdb as my pluggabe database
Logged in sqlplus as connect sys as sysdba
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE SCOPE=BOTH;
create user c##test identified by …
|
| 11 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 17 April 2024 16:46 «»
By: Barbara Boehmer
Re: Oracle ORA-00918: column ambiguously defined
…I am trying to understand what causes an error, I try to narrow it down to the simplest example that I can that reproduces the error and the simplest thing that resolves it. Please see the simplified demonstration below in which the problem is …
|
| 12 |
Forum: SQL & PL/SQL «»
Posted on: Sun, 17 December 2023 20:26 «»
By: Barbara Boehmer
Re: Problem in pivot query
…@OraFerro,
I strongly suspect that you have a minor typing error in your query that is not being posted here. You need to post a copy and paste of an actual complete run as I have done below, including the view creation, including line numbers, …
|
| 13 |
Forum: Server Utilities «»
Posted on: Fri, 28 July 2023 12:28 «»
By: Barbara Boehmer
Re: how to parametrize values in pl/sql block
…see the changes to line 34 below. It should work with multiple tables or just one. It will just use an IN clause instead of =.
SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
2 (p_schema in …
|
| 14 |
Forum: Server Utilities «»
Posted on: Fri, 28 July 2023 04:49 «»
By: Barbara Boehmer
Re: how to parametrize values in pl/sql block
…is an example for you.
SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
2 (p_schema in varchar2,
3 p_table in varchar2)
4 as
5 l_dp_handle …
|
| 15 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 03 February 2023 15:28 «»
By: Barbara Boehmer
Re: XML parse
…would help to know where your data comes from, how you retrieve it, how it is stored, and what sort of table you want to load it into. In the following I have assumed that it is in a text file on your server. I suggest that you start with the basics and…
|
| 16 |
Forum: Text & interMedia «»
Posted on: Mon, 16 January 2023 11:28 «»
By: Barbara Boehmer
Re: Oracle Text Search - Handling special characters and blank search term
…the translate function, it translates each occurrence of the character in the second parameter to the corresponding character in the third parameter, but just removes others. In the original example, there were only three special characters, so I used …
|
| 17 |
Forum: Text & interMedia «»
Posted on: Tue, 10 January 2023 12:15 «»
By: Barbara Boehmer
Re: Oracle Text Search - Handling special characters and blank search term
…are a number of issues here. One issue is that Oracle Text does not know what to do with the special characters. Another is that if the text query is not executed and there is no score then it does not know what to do when you have selected that …
|
| 18 |
Forum: SQL & PL/SQL «»
Posted on: Sat, 16 December 2023 17:29 «»
By: Barbara Boehmer
Re: Problem in pivot query
…sum(wtd_year) to sum(wtd_amount)
SCOTT@orcl_12.1.0.2.0> select banner from v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12…
|
| 19 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 18 September 2023 11:41 «»
By: Barbara Boehmer
Re: how to get employee in and out time for generated query based on first in and last out
…the following minimal example, dates with only one punch time will have the same date for in and out times. Also, whatever your first column is "M." (machine?) is ignored. It is ordered by the punch_date, which is really just a …
|
| 20 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 07 February 2023 13:48 «»
By: Barbara Boehmer
Re: XML parse
…are two issues here.
You have multiple LEI under different categories, so those can be given different column names, just as the different Id were named FrId and ToId.
You have multiple Rpt under TradData, so you need an additional XMLTABLE. You …
|
| 21 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 06 November 2023 18:02 «»
By: Barbara Boehmer
Re: Recursive subquery factoring
…following applies a function to one column of your existing view.
-- function and query:
create or replace function get_path_id_post_d
(p_path_id_post in varchar2)
return varchar2
as
v_path_id_post_d &…
|
| 22 |
Forum: SQL & PL/SQL «»
Posted on: Sun, 01 October 2023 03:57 «»
By: Barbara Boehmer
Re: Grouping similar strings together using UTL_MATCH Jaro-Winkler
…following produces the same results as your query, but in a slightly different format. It eliminates the possibility of having things that are not closely related in the same group, due to a chain of relations, by comparing collections and only …
|
| 23 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 29 March 2023 19:55 «»
By: Barbara Boehmer
Re: sql query
…
SCOTT@orcl_12.1.0.2.0> COLUMN mod_flag FORMAT A8
SCOTT@orcl_12.1.0.2.0> -- test data you provided:
SCOTT@orcl_12.1.0.2.0> WITH
2 students (id, term, module) AS
3 (SELECT 123, 2023, 2100 FROM DUAL UNION ALL
4 SELECT 123, …
|
| 24 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 03 March 2023 11:24 «»
By: Barbara Boehmer
Re: Identify Groups where 2 or more Clients have bought the same Products - SQL Oracle
…for creating table and data that you should have provided; please do so yourself next time:
CREATE TABLE table1
(group_id VARCHAR2(8),
client_id VARCHAR2(9),
product_id VARCHAR2(10),
prod_qty NUMBER)
/
INSERT ALL
INTO table1 …
|
| 25 |
Forum: Forms «»
Posted on: Sat, 13 August 2022 03:29 «»
By: sasipalarivattom
Re: Files generated on server with sufix
…Hi,
How are you generating the report?
Have you tried generating report in URL
like http://WIN-SERV:9002/reports/rwservlet?server=rep_server1&report=test.rdf&destype=file&desformat=html&userid=scott/ti ger@orcl&desname=c:\rep1.…
|
| 26 |
Forum: Networking and Gateways «»
Posted on: Sun, 04 October 2020 12:31 «»
By: Michel Cadot
Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider)
…
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
1/
I doubt your application service name is "orclXDB&…
|
| 27 |
Forum: Networking and Gateways «»
Posted on: Sun, 04 October 2020 12:04 «»
By: pdsQsql
ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) (2 merged)
…Hello,
I am having issue with connecting oracle from Sql Server using Linked Server.
I have installed Oracle client and Sql Server on both the same server.
I have tested TNSPING ORCL coming fine
C:\Windows\system32>TNSPING orcl
TNS Ping …
|
| 28 |
Forum: Server Utilities «»
Posted on: Wed, 10 October 2018 09:29 «»
By: Barbara Boehmer
Re: Rows not loaded because all WHEN clauses were failed.
…every when clause after the first when clause, you need to use POSITION(1) for the first field. Otherwise, it expects the next fields after the positions of the fields in the previous when clause.
Also, RECORD has special meaning, so if it is a …
|
| 29 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 27 July 2018 20:13 «»
By: Barbara Boehmer
Re: Possibility of Name
…following assumes that you want at least 2 of the names in a string that may contain 2 or 3 or more names to match the names in one value of the name column of the table, in any order.
-- test data:
SCOTT@orcl_12.1.0.2.0> select name from …
|
| 30 |
Forum: Text & interMedia «»
Posted on: Thu, 26 July 2018 19:38 «»
By: Barbara Boehmer
Re: Oracle Text Performance
…ORA-20000 is a user-defined error that Oracle Text uses in its applications and is called using raise_application_error to display the message "Oracle Text error", so that you know that the error originated from something to do with Oracle Text…
|
| 31 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 27 March 2018 17:38 «»
By: Barbara Boehmer
Re: how to use variable to store comma seperated values
…you want to use some version of Tom Kyte's string to table methods, then you could do something like below.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM demo
2 /
ID NAME HIREDATE…
|
| 32 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 27 March 2018 16:29 «»
By: Barbara Boehmer
Re: how to use variable to store comma seperated values
…code contains a lot of errors. Please see the demonstration below using corrected code and a demo table to demonstrate the usage of the code.
-- demo table for demonstration:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM demo
2 /
ID NAME…
|
| 33 |
Forum: Forms «»
Posted on: Mon, 12 March 2018 17:25 «»
By: Barbara Boehmer
Re: Asking for search algorithm
…have copied and pasted what you posted, removed the line numbers, and run it, but do not get the same results. I get one row from the last query. I am baffled as to why there is such a difference. I have created a script below with some modifications …
|
| 34 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 31 January 2018 01:09 «»
By: mvrkr44
Re: need two token matching query
…Barbara Boehmer ,
Thanks for your support,this may be the last post on this topic .
.Please suggest any changes need for the below.
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number…
|
| 35 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 22 January 2018 06:08 «»
By: Barbara Boehmer
Re: need two token matching query
…need to post a copy and paste of exactly what you have done, not just describe it. For each special character, you will need to add it to the printjoins. If it is a punctuation, then you will also need to remove it from the punctuations. If you remove …
|
| 36 |
Forum: General «»
Posted on: Fri, 05 January 2018 21:54 «»
By: dba4oracle
Re: SQL Report running manually not through cron
…captured env in file while running in cron,as expected env have all values properly
below is env.capture
HOME=/data/myuser/user1
HOST=IAPL1
LANG=en_US.UTF-8
LOGNAME=user1
MAILTO=myuser@mycompany.com
ORACLE_HOME=/opt/u01/app/oracle/…
|
| 37 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 20 December 2017 12:58 «»
By: Barbara Boehmer
Re: need two token matching query
…
SCOTT@orcl_12.1.0.2.0> -- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 firstname varchar2(100),
6 secondname varchar2(100),
7 …
|
| 38 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 08 December 2017 01:55 «»
By: Barbara Boehmer
Re: need two token matching query
…
Then I would suggest something like what Solomon suggested:
-- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names(id number,firstname varchar2(100),secondname varchar2(100),thirdname varchar2(100),fourthname varchar2(100),…
|
| 39 |
Forum: SQL & PL/SQL «»
Posted on: Thu, 30 November 2017 19:11 «»
By: Barbara Boehmer
Re: regexp_replace() function help
…following just answers the original question, without regard to its purpose. It separates the strings between commas, makes the replacements, then concatenates them together again.
SCOTT@orcl_12.1.0.2.0> with test_data as
2 (select 'MUNI_ID…
|
| 40 |
Forum: Text & interMedia «»
Posted on: Fri, 10 November 2017 20:33 «»
By: Barbara Boehmer
Re: where to check context index sync status
…can use a log as demonstrated below.
SCOTT@orcl_12.1.0.2.0> create table test1(names varchar2(1000));
Table created.
SCOTT@orcl_12.1.0.2.0> create index idx_names on test1(names) indextype is ctxsys.context;
Index created.
SCOTT@…
|
Blog
Search
Help
Members
Register
Login
Home